drop table spmi_tmp.dm_operation_bill_fee_daily_summary_tmp_dt;
CREATE EXTERNAL TABLE  spmi_tmp.dm_operation_bill_fee_daily_summary_tmp_dt(
    bill_generation_date                          string comment '账单产生日期'
    , bill_generation_time_date                   string comment '账单业务发生日期'
    , operation_financial_center_id               string comment '操作财务中心ID'
    , operation_financial_center_code             string comment '操作财务中心名称'
    , operation_financial_center_name             string comment '操作财务中心编码'
    , operation_network_id                        string comment '操作网点ID'
    , operation_network_code                      string comment '操作网点编码'
    , operation_network_name                      string comment '操作网点名称'
    , send_financial_center_id                    string comment '寄件财务中心'
    , send_financial_center_code                  string comment '寄件财务中心名称'
    , send_financial_center_name                  string comment '寄件财务中心编码'
    , send_network_id                             string comment '寄件网点ID'
    , send_network_code                           string comment '寄件网点编码'
    , send_network_name                           string comment '寄件网点名称'
    , dec_network_id                              string comment '结算网点'
    , dec_network_code                            string comment '结算网点编码'
    , dec_network_name                            string comment '结算网点名称'
    , transport_type_id                           string comment '运输方式ID'
    , transport_type_code                         string comment '运输方式名称'
    , transport_type_name                         string comment '运输方式编码'
    , receiver_province_id                        string comment '收件省份ID'
    , receiver_province_name                      string comment '收件省份名称'
    , unloading_package_financial_center_id       string comment '拆包财务中心ID'
    , unloading_package_financial_center_code     string comment '拆包财务中心CODE'
    , unloading_package_financial_center_name     string comment '拆包财务中心名称'
    , fee_type_id                                 string comment '费用类型ID 1 操作费'
    , fee_type_name                               string comment '费用类型名称'
    , is_package_cost                             string comment '包计费标识1是2否'
    , sum_waybill_piece                           bigint comment '运单件数汇总'
    , sum_waybill_num                             bigint comment '运单票数汇总'
    , sum_waybill_weight                          double comment '运单重量汇总'
    , sum_package_charge_weight                   double comment '计费重量汇总'
    , sum_settle_weight                           double comment '结算重量汇总'
    , sum_fee_cost                                double comment '费用金额汇总'
    , count_on_fee_cost                           bigint comment '费用为0票数'
    , sum_red_sign                                bigint comment '红冲标识汇总'
  )
comment '操作费账单日汇总临时表'
PARTITIONED BY (
  `dt` string COMMENT '账单产生日期')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/spmi_tmp.db/external/dm_operation_bill_fee_daily_summary_tmp_dt'
;



drop table spmi_dm.dm_operation_bill_fee_daily_summary;
CREATE EXTERNAL TABLE spmi_dm.dm_operation_bill_fee_daily_summary(
      bill_date                                   string comment '账单日期'
    , date_type                                   string comment '日期类型'
    , operation_financial_center_id               string comment '操作财务中心ID'
    , operation_financial_center_code             string comment '操作财务中心名称'
    , operation_financial_center_name             string comment '操作财务中心编码'
    , operation_network_id                        string comment '操作网点ID'
    , operation_network_code                      string comment '操作网点编码'
    , operation_network_name                      string comment '操作网点名称'
    , send_financial_center_id                    string comment '寄件财务中心'
    , send_financial_center_code                  string comment '寄件财务中心名称'
    , send_financial_center_name                  string comment '寄件财务中心编码'
    , send_network_id                             string comment '寄件网点ID'
    , send_network_code                           string comment '寄件网点编码'
    , send_network_name                           string comment '寄件网点名称'
    , dec_network_id                              string comment '结算网点'
    , dec_network_code                            string comment '结算网点编码'
    , dec_network_name                            string comment '结算网点名称'
    , transport_type_id                           string comment '运输方式ID'
    , transport_type_code                         string comment '运输方式名称'
    , transport_type_name                         string comment '运输方式编码'
    , dec_franchisee_id                           string comment '加盟商ID'
    , dec_franchisee_code                         string comment '加盟商CODE'
    , dec_franchisee_name                         string comment '加盟商名称'
    , receiver_province_id                        string comment '收件省份ID'
    , receiver_province_name                      string comment '收件省份名称'
    , unloading_package_financial_center_id       string comment '拆包财务中心ID'
    , unloading_package_financial_center_code     string comment '拆包财务中心CODE'
    , unloading_package_financial_center_name     string comment '拆包财务中心名称'
    , fee_type_id                                 string comment '费用类型ID 1 操作费'
    , fee_type_name                               string comment '费用类型名称'
    , is_package_cost                             string comment '包计费标识1是2否'
    , sum_waybill_piece                           bigint comment '运单件数汇总'
    , sum_waybill_num                             bigint comment '运单票数汇总'
    , sum_waybill_weight                          double comment '运单重量汇总'
    , sum_package_charge_weight                   double comment '计费重量汇总'
    , sum_settle_weight                           double comment '结算重量汇总'
    , sum_fee_cost                                double comment '费用金额汇总'
    , count_on_fee_cost                           bigint comment '费用为0票数'
  )
comment '操作费账单日汇总'
 PARTITIONED BY (
   `dt` string COMMENT '账单日期')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://yl-bg-hdp/dw/hive/spmi_dm.db/external/dm_operation_bill_fee_daily_summary'
;

drop table spmi_tmp.dm_operation_bill_fee_daily_summary_push;
CREATE EXTERNAL TABLE spmi_tmp.dm_operation_bill_fee_daily_summary_push(
    id                                            string comment 'ID'
    , bill_date                                   string comment '账单日期'
    , date_type                                   string comment '日期类型'
    , operation_financial_center_id               string comment '操作财务中心ID'
    , operation_financial_center_code             string comment '操作财务中心名称'
    , operation_financial_center_name             string comment '操作财务中心编码'
    , operation_network_id                        string comment '操作网点ID'
    , operation_network_code                      string comment '操作网点编码'
    , operation_network_name                      string comment '操作网点名称'
    , send_financial_center_id                    string comment '寄件财务中心'
    , send_financial_center_code                  string comment '寄件财务中心名称'
    , send_financial_center_name                  string comment '寄件财务中心编码'
    , send_network_id                             string comment '寄件网点ID'
    , send_network_code                           string comment '寄件网点编码'
    , send_network_name                           string comment '寄件网点名称'
    , dec_network_id                              string comment '结算网点'
    , dec_network_code                            string comment '结算网点编码'
    , dec_network_name                            string comment '结算网点名称'
    , transport_type_id                           string comment '运输方式ID'
    , transport_type_code                         string comment '运输方式名称'
    , transport_type_name                         string comment '运输方式编码'
    , dec_franchisee_id                           string comment '加盟商ID'
    , dec_franchisee_code                         string comment '加盟商CODE'
    , dec_franchisee_name                         string comment '加盟商名称'
    , receiver_province_id                        string comment '收件省份ID'
    , receiver_province_name                      string comment '收件省份名称'
    , unloading_package_financial_center_id       string comment '拆包财务中心ID'
    , unloading_package_financial_center_code     string comment '拆包财务中心CODE'
    , unloading_package_financial_center_name     string comment '拆包财务中心名称'
    , fee_type_id                                 string comment '费用类型ID 1 操作费'
    , fee_type_name                               string comment '费用类型名称'
    , is_package_cost                             string comment '包计费标识1是2否'
    , sum_waybill_piece                           bigint comment '运单件数汇总'
    , sum_waybill_num                             bigint comment '运单票数汇总'
    , sum_waybill_weight                          double comment '运单重量汇总'
    , sum_package_charge_weight                   double comment '计费重量汇总'
    , sum_settle_weight                           double comment '结算重量汇总'
    , sum_fee_cost                                double comment '费用金额汇总'
    , count_on_fee_cost                           bigint comment '费用为0票数'
    , create_time                                 timestamp comment '更新时间'
)
comment '操作费账单日汇总推送表'
STORED AS AVRO
LOCATION 'hdfs://yl-bg-hdp/dw/hive/spmi_tmp.db/external/dm_operation_bill_fee_daily_summary_push'
;